﻿using KPIS.GERP.Models;
using KPIS.GERP.Models.Class;
using KPIS.GERP.Models.ICM;
using System;
using System.Collections.Generic;
using System.Linq;
using System.Linq.Dynamic;
using System.Linq.Expressions;
using System.Runtime.Serialization;
using System.ServiceModel;
using System.Text;
using System.Data;
using System.Data.Entity;
using System.Web.Mvc;
using KPIS.GERP.Models.SYS;


namespace KPIS.GERP.WCF.GIMS
{
    [ServiceContract]
    public interface IReceiveParcelService
    {
        #region Receive

        [OperationContract]
        IEnumerable<Received> SelectReceived(Received criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        int Insert(Received model, int intCreateByUserId);

        [OperationContract]
        void Update(Received model, int updateByUserId);

        [OperationContract]
        void Delete(int id, int deleteByUserId);

        #endregion

        #region ReceiveDtl

        [OperationContract]
        IEnumerable<ReceiveDtl> SelectReceiveDtl(ReceiveDtl criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        int InsertDtl(ReceiveDtl model, int intCreateByUserId);

        [OperationContract]
        void DeleteDtl(int id, int deleteByUserId);

        #endregion

        #region Asset

        [OperationContract]
        IEnumerable<Asset> SelectAsset(Asset criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        int InsertAsset(Asset model, int intCreateByUserId);

        [OperationContract]
        string MaxCodeAsset(string CompareAssetCode);

        [OperationContract]
        string MaxCodeOfficeSupplyLot(string CompareOfficeCode);

        #endregion

        #region OfficeSupplyLot

        [OperationContract]
        int InsertOfficeSupplyLot(OfficeSuppliesLot model, int intCreateByUserId);

        #endregion

        #region AssetWarranty

        [OperationContract]
        IEnumerable<AssetWarranty> SelectAssetWarranty(AssetWarranty criteriaModel = null, List<string> includeSubObjectString = null);

        [OperationContract]
        int InsertWarranty(int id, string no, decimal price, int time, string start, string end, int createBy);

        [OperationContract]
        void DeleteWarranty(int id, int deleteByUserId);

        #endregion

        #region String[]

        [OperationContract]
        string[] GetParcelInformation(int intDnItemId);

        [OperationContract]
        string[] GetWarehouse(int intDnId);
        
        [OperationContract]
        string[] GetDpRate(int intDpTypeId);

        [OperationContract]
        string[] GetParcelInfo(int intResItemId);

        #endregion

        #region SelectList

        [OperationContract]
        List<SelectListItem> DeliveryNoteSelectList();

        [OperationContract]
        List<SelectListItem> UnitSelectList();

        [OperationContract]
        List<SelectListItem> MasLookUpSelectList(IcmMasLookUpType.LookUpType lookUpType);

        [OperationContract]
        List<SelectListItem> DepreciationType();

        [OperationContract]
        List<SelectListItem> ParcelSelectList(int intDnId);

        [OperationContract]
        IList<SelectListItem> SelectListWarehouse(Warehouse criteriaModel = null);

        [OperationContract]
        IList<SelectListItem> SelectListMasResourceType(MasResourceType criteriaModel = null);

        [OperationContract]
        IList<SelectListItem> SelectListMasResourceGroup(MasResourceGroup criteriaModel = null);

        [OperationContract]
        IList<SelectListItem> SelectListMasResourceSubGroup(MasResourceSubGroup criteriaModel = null);

        [OperationContract]
        IList<SelectListItem> SelectListMasResourceItem(MasResourceItem criteriaModel = null);

        #endregion


    }

    [ServiceErrorBehavior(typeof(ElmahErrorHandler))]
    public class ReceiveParcelService : IReceiveParcelService
    {
        #region Receive

        public IEnumerable<Received> SelectReceived(Received criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<Received> query = context.Receiveds;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    //var receivedTops = query.ToList();
                    //try
                    //{
                    //    foreach (var receivedTop in receivedTops)
                    //    {
                    //        receivedTop.Warehouse.Receiveds = null;
                    //    }
                    //}
                    //catch { }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int Insert(Received model, int intCreateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = intCreateByUserId;
                    model.RecordStatus = RecordStatus.UsedStatus;
                    model.CreatedWhen = DateTime.Now;
                    model.ReceiveCode = CodeGenerate.GetNextCode(CodeGenerate.TransectionType.ReceiveParcel, "");
                    if (string.IsNullOrWhiteSpace(model.ReceiveCode))
                    {
                        model.ReceiveCode = CodeGenerate.GetNextCode(CodeGenerate.TransectionType.ReceiveParcel);
                    } 
                    context.Receiveds.Add(model);
                    context.SaveChanges();

                    return model.ReceiveId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public void Update(Received model, int updateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.UpdatedBy = updateByUserId;
                    model.UpdatedWhen = DateTime.Now;
                    context.Entry(model).State = EntityState.Modified;
                    
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        public void Delete(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.Receiveds.Find(id);
                    if (modelToDelete == null) return;
                    
                    context.Entry(modelToDelete).State = EntityState.Modified;
                    modelToDelete.UpdatedBy = deleteByUserId;
                    modelToDelete.UpdatedWhen = DateTime.Now;
                    modelToDelete.RecordStatus = Models.SYS.RecordStatus.DeletedStatus;
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        #endregion

        #region ReceiveDtl
        
        public IEnumerable<ReceiveDtl> SelectReceiveDtl(ReceiveDtl criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<ReceiveDtl> query = context.ReceiveDtls;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    return x;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertDtl(ReceiveDtl model, int intCreateByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = intCreateByUserId;
                    model.RecordStatus = RecordStatus.UsedStatus;                    
                    model.CreatedWhen = DateTime.Now;
                    context.ReceiveDtls.Add(model);
                    context.SaveChanges();

                    return model.ReceiveDtlId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public void DeleteDtl(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.ReceiveDtls.Find(id);
                    if (modelToDelete == null) return;

                    context.Entry(modelToDelete).State = EntityState.Modified;
                    modelToDelete.UpdatedBy = deleteByUserId;
                    modelToDelete.UpdatedWhen = DateTime.Now;
                    modelToDelete.RecordStatus = "D";

                    foreach (var ol in
                        context.OfficeSuppliesLots.Where(o => o.ReceiveDtlId == modelToDelete.ReceiveDtlId))
                    {
                        context.Entry(ol).State = EntityState.Modified;
                        ol.UpdatedBy = deleteByUserId;
                        ol.UpdatedWhen = DateTime.Now;
                        ol.RecordStatus = "D";
                    }

                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }

        }

        
        #endregion

        #region Asset

        public IEnumerable<Asset> SelectAsset(Asset criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<Asset> query = context.Assets;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertAsset(Asset model, int intCreateBy)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = intCreateBy;
                    model.RecordStatus = RecordStatus.UsedStatus;
                    model.CreatedWhen = DateTime.Now;
                    context.Assets.Add(model);
                    context.SaveChanges();

                    return model.AssetId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        

        #endregion

        #region OfficeSupplyLot

        public int InsertOfficeSupplyLot(OfficeSuppliesLot model, int intCreateBy)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    model.CreatedBy = intCreateBy;
                    model.RecordStatus = RecordStatus.UsedStatus;
                    model.CreatedWhen = DateTime.Now;
                    context.OfficeSuppliesLots.Add(model);
                    context.SaveChanges();

                    return model.OfficeSuppliesLotId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public string MaxCodeAsset(string CompareAssetCode)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var MaxCode = (from a in context.Assets
                                   where a.AssetCode.Contains(CompareAssetCode)
                                   select a.AssetCode).Max();

                    //var RunningNo = MaxCode.Substring(3, 3) + 1;

                    return MaxCode;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        public string MaxCodeOfficeSupplyLot(string CompareOfficeCode)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var MaxCode = (from a in context.OfficeSuppliesLots
                                   where a.LotNo.Contains(CompareOfficeCode)
                                   select a.LotNo).Max();

                    //var RunningNo = MaxCode.Substring(3, 3) + 1;

                    return MaxCode;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }

        #endregion

        #region AssetWarranty

        public IEnumerable<AssetWarranty> SelectAssetWarranty(AssetWarranty criteriaModel = null, List<string> includeSubObjectString = null)
        {
            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<AssetWarranty> query = context.AssetWarrantys;

                    if (includeSubObjectString != null)
                    {
                        query = includeSubObjectString.Aggregate(query, (current, includePath) => current.Include(includePath));
                    }

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    return query.ToList();
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }
        }

        public int InsertWarranty(int id, string no, decimal price, int time, string start, string end, int createBy)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    AssetWarranty model = new AssetWarranty();

                    model.AssetId = id;
                    model.WarrantyNo = no;
                    model.WarrantyPrice = price;
                    model.WarrantyTime = time;
                    model.WarrantyStartDate = Convert.ToDateTime(start);
                    model.WarrantyExpireDate = Convert.ToDateTime(end);
                    model.CreatedBy = createBy;
                    model.CreatedWhen = DateTime.Now;
                    model.RecordStatus = RecordStatus.UsedStatus;

                    context.AssetWarrantys.Add(model);
                    context.SaveChanges();

                    return model.WarrantyId;
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

        }
        
        public void DeleteWarranty(int id, int deleteByUserId)
        {
            try
            {
                using (var context = new GERPContext())
                {
                    var modelToDelete = context.AssetWarrantys.Find(id);
                    if (modelToDelete == null) return;

                    context.Entry(modelToDelete).State = EntityState.Modified;
                    modelToDelete.UpdatedBy = deleteByUserId;
                    modelToDelete.UpdatedWhen = DateTime.Now;
                    modelToDelete.RecordStatus = "D";
                    context.SaveChanges();
                }
            }
            catch (Exception ex)
            {

                throw ex;
            }
        }

        #endregion

        #region String[]

        public string[] GetParcelInformation(int intDnItemId)
        {
            string strUnitId = "";
            string strUnitName = "";
            string strTypeId = "";
            string strTypeName = "";
            string strProjName = "";
            string strProjId = "";
            string strPrice = "0";
            string strResItemId = "";
            string strSubGrpId = "";
            string strGrpId = "";
            string strQuantity = "0";
            string strResItemCode = "";
            

            var context = new GERPContext(false);
            try
            {
                var query = (from a in context.DeliveryNotes
                             join b in context.DeliveryNoteItems on a.DnId equals b.DnId
                             //join c in context.PurchaseOrderInstallmentItems on b.PoInstallItemId equals c.PoInstallItemId
                             //join d in context.BidderQuotations on c.BidderQuoId equals d.BidderQuoId
                             //join e in context.PurchaseRequestItems on d.PrItemId equals e.PrItemId
                             //join f in context.PrePurchaseRequestItems on e.PrItemId equals f.PrItemId
                             join g in context.ProjectOperationExpenses on a.ProjOperExpId equals g.ProjOperExpId
                             join h in context.ProjectExpenses on g.ProjExpId equals h.ProjExpId
                             join i in context.Projects on h.ProjId equals i.ProjId
                             join j in context.MasResourceItems on b.ResItemId equals j.ResItemId
                             where b.DnItemId == intDnItemId
                             select new
                             {
                                 UnitId = b.MasResourceItem.UnitId,
                                 UnitName = b.MasResourceItem.MasUnit.Name,
                                 TypeId = b.MasResourceItem.MasResourceSubGroup.MasResourceGroup.MasResourceType.ResTypeId,
                                 TypeName = b.MasResourceItem.MasResourceSubGroup.MasResourceGroup.MasResourceType.Name,
                                 ProjId = i.ProjId,
                                 ProjName = i.Name,
                                 Price = j.StdPrice,
                                 ResItemId = b.ResItemId,
                                 SubGrpId = b.MasResourceItem.MasResourceSubGroup.ResSubGrpId,
                                 GrpId = b.MasResourceItem.MasResourceSubGroup.MasResourceGroup.ResGrpId,
                                 Quantity = b.ItemQty,
                                 ResItemCode = j.Code
                             }).ToList();

                if (query != null)
                {
                    foreach (var result in query)
                    {
                        strUnitId = result.UnitId.ToString();
                        strUnitName = result.UnitName.ToString();
                        strTypeId = result.TypeId.ToString();
                        strTypeName = result.TypeName.ToString();
                        strProjId = result.ProjId.ToString();
                        strProjName = result.ProjName.ToString();
                        strPrice = result.Price.ToString();
                        strResItemId = result.ResItemId.ToString();
                        strSubGrpId = result.SubGrpId.ToString();
                        strGrpId = result.GrpId.ToString();
                        strQuantity = result.Quantity.ToString();
                        strResItemCode = result.ResItemCode;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return new string[] { strUnitId, strUnitName, strTypeId, strTypeName, strProjId, strProjName, strPrice, strResItemId, strSubGrpId, strGrpId, strQuantity, strResItemCode };
        }

        public string[] GetWarehouse(int intDnId)
        {
            string strWhId = "";
            string strWhName = "";

            var context = new GERPContext(false);
            try
            {
                var query = (from a in context.DeliveryNotes
                             join b in context.Warehouses on a.WhId equals b.WhId
                             where a.DnId == intDnId && a.RecordStatus != RecordStatus.DeletedStatus
                             select new { a.WhId, b.Name }).ToList();


                if (query != null)
                {
                    foreach (var result in query)
                    {
                        strWhId = result.WhId.ToString();
                        strWhName = result.Name;
                    }
                }
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return new string[] { strWhId, strWhName };
        }

        public string[] GetDpRate(int intDpTypeId)
        {
            string strDpRate;
            string strMinLifeYear;
            string strMaxLifeYear;
            var context = new GERPContext(false);
            try
            {
                var Rate = (from m in context.MasDepreciationTypes
                            where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                            && m.DpTypeId == intDpTypeId
                            select m).FirstOrDefault();
                strDpRate = Rate.DpRateYear.ToString();
                strMinLifeYear = Rate.MinLifeYear.ToString();
                strMaxLifeYear = Rate.MaxLifeYear.ToString();
            }
            catch (Exception ex)
            {
                throw ex;
            }

            return new string[] { strDpRate, strMinLifeYear, strMaxLifeYear };
        }

        public string[] GetParcelInfo(int intResItemId)
        {
            string strGroupId = "";
            string strSupGroupId = "";
            string strResItemCode = "";
            string strResTypeName = "";

            var context = new GERPContext(false);
            try
            {
                var info = (from a in context.MasResourceItems
                            join b in context.MasResourceSubGroups on a.ResSubGrpId equals b.ResSubGrpId
                            join c in context.MasResourceGroups on b.ResGrpId equals c.ResGrpId
                            join d in context.MasResourceTypes on c.ResTypeId equals d.ResTypeId
                            where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                            && b.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                            && c.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                            && d.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                            && a.ResItemId == intResItemId
                            select
                            new
                            {
                                GrpId = c.ResGrpId,
                                SubGrpId = b.ResSubGrpId,
                                strResItemCode = a.Code,
                                ResTypeName = d.Name
                            }).ToList();

                if (info != null)
                {
                    foreach (var result in info)
                    {
                        strGroupId = result.GrpId.ToString();
                        strSupGroupId = result.SubGrpId.ToString();
                        strResItemCode = result.strResItemCode.ToString();
                        strResTypeName = result.ResTypeName;
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return new string[] { strGroupId, strSupGroupId, strResItemCode, strResTypeName };
        }

        #endregion

        #region SelectList

        public List<SelectListItem> DeliveryNoteSelectList()
        {
            var data = new List<SelectListItem>();
            var db = new GERPContext();
            try
            {
                var query1 = (from m in db.DeliveryNotes.Include("PurchaseOrderInstallment").Include("PurchaseOrder")
                              where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                              select m);

                var query2 = (from m in db.DeliveryNotes
                              join n in db.Receiveds on m.DnId equals n.DnId
                              where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                              && n.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                              select m);

                var query3 = query1.Except(query2);

                var query = query3.ToList();

                //List<Models.ICM.DeliveryNote> listDeliveryNote = query;

                var query4 = db.DeliveryNoteItems.ToList();

                foreach (var DeliveryNotes in query)
                {
                    var dnId = DeliveryNotes.DnId;
                    var query5 = query4.Where(o => o.DnId == dnId && o.ResItemId.HasValue && (o.HaveItem ?? true));

                    if (query5 != null && query5.Count() > 0)
                    {
                        var strText = string.Format("{0} - {1} [{5} งวด {3}/{4} มี {2} รายการ] ",
                        DeliveryNotes.Code,
                        DeliveryNotes.Name,
                        query4.Count(o => o.DnId == dnId),
                        //query5.Any() ? " - ไม่ระบุ " + query5.Count() + "รายการ" : "",
                        DeliveryNotes.PurchaseOrderInstallment.InstallNumber,
                        db.PurchaseOrderInstallments.Count(o => o.PoId == DeliveryNotes.PoId),
                        (DeliveryNotes.PurchaseOrder != null ? DeliveryNotes.PurchaseOrder.Code : ""));

                        var item = new SelectListItem
                        {
                            Text = strText,
                            Value = DeliveryNotes.DnId.ToString()
                        };

                        data.Add(item);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return data;
        }

        public List<SelectListItem> UnitSelectList()
        {
            var data = new List<SelectListItem>();
            var db = new GERPContext();
            try
            {
                List<Models.ICM.MasUnit> listUnit = (from m in db.MasUnits
                                                     where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                                     select m).ToList();

                foreach (var Unit in listUnit)
                {
                    var item = new SelectListItem { Text = Unit.Name, Value = Unit.UnitId.ToString() };
                    data.Add(item);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return data;
        }

        public List<SelectListItem> MasLookUpSelectList(IcmMasLookUpType.LookUpType lookUpType)
        {
            var data = new List<SelectListItem>();
            using (var db = new GERPContext(false))
            {
                try
                {
                    List<Models.ICM.IcmMasLookUp> listMasLookUp = (from m in db.IcmMasLookUps
                                                                where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                                                      && m.LookUpTypeId == (int)lookUpType
                                                                select m).ToList();

                    foreach (var MasLookUp in listMasLookUp)
                    {
                        var item = new SelectListItem { Text = MasLookUp.LookUpName, Value = MasLookUp.LookUpId.ToString() };
                        if (MasLookUp.LookUpId == (int)lookUpType) item.Selected = true;
                        data.Add(item);
                    }

                }
                catch (Exception ex)
                {
                    throw ex;
                }
            }
            return data;
        }

        public List<SelectListItem> DepreciationType()
        {
            var data = new List<SelectListItem>();
            var db = new GERPContext();
            try
            {
                List<Models.ICM.MasDepreciationType> listMasDepreciationType = (from m in db.MasDepreciationTypes
                                                                                where m.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                                                                                select m).ToList();

                foreach (var DepreciationType in listMasDepreciationType)
                {
                    var item = new SelectListItem { Text = DepreciationType.Name, Value = DepreciationType.DpTypeId.ToString() };
                    data.Add(item);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return data;
        }

        public List<SelectListItem> ParcelSelectList(int intDnId)
        {
            var data = new List<SelectListItem>();
            var db = new GERPContext();
            try
            {
                var query1 = (from a in db.DeliveryNotes
                             join b in db.DeliveryNoteItems on a.DnId equals b.DnId
                             join c in db.MasResourceItems on b.ResItemId equals c.ResItemId                             
                             where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                             && a.DnId == intDnId
                             select b).ToList();

                var query2 = (from a in db.DeliveryNotes
                              join b in db.DeliveryNoteItems on a.DnId equals b.DnId
                              join c in db.MasResourceItems on b.ResItemId equals c.ResItemId
                              join f in db.Receiveds on a.DnId equals f.DnId
                              join g in db.ReceiveDtls on b.DnItemId equals g.DeliveryNoteItemId                              
                              where a.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                              && g.RecordStatus != Models.SYS.RecordStatus.DeletedStatus
                              && f.DnId == intDnId
                              select b).ToList();

                var querySub = query1.Except(query2).ToList();

                var list = (from a in querySub
                            join b in db.MasResourceItems on a.ResItemId equals b.ResItemId
                            select new { name=b.Name, val=a.DnItemId }).ToList();

                foreach (var Parcel in list)
                {
                    var item = new SelectListItem { Text = Parcel.name, Value = Parcel.val.ToString() };
                    data.Add(item);
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return data;
        }
        
        public IList<SelectListItem> SelectListWarehouse(Warehouse criteriaModel = null)
        {
            var list = new List<SelectListItem>();
            list.Add(new SelectListItem { Selected = true, Text = "....เลือกคลังพัสดุ....", Value = "" });

            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<Warehouse> query = context.Warehouses;

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    foreach (var model in x)
                    {
                        var item = new SelectListItem { Text = model.Name, Value = model.WhId.ToString() };
                        list.Add(item);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }

        public IList<SelectListItem> SelectListMasResourceType(MasResourceType criteriaModel = null)
        {
            var list = new List<SelectListItem>();
            list.Add(new SelectListItem { Selected = true, Text = "....เลือกประเภทพัสดุ....", Value = "" });

            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<MasResourceType> query = context.MasResourceTypes;

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    foreach (var model in x)
                    {
                        var item = new SelectListItem { Text = model.Name, Value = model.ResTypeId.ToString() };
                        list.Add(item);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }

        public IList<SelectListItem> SelectListMasResourceGroup(MasResourceGroup criteriaModel = null)
        {
            var list = new List<SelectListItem>();
            list.Add(new SelectListItem { Selected = true, Text = "....เลือกชนิดพัสดุ....", Value = "" });

            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<MasResourceGroup> query = context.MasResourceGroups;

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    foreach (var model in x)
                    {
                        var item = new SelectListItem { Text = model.Name, Value = model.ResGrpId.ToString() };
                        list.Add(item);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }

        public IList<SelectListItem> SelectListMasResourceSubGroup(MasResourceSubGroup criteriaModel = null)
        {
            var list = new List<SelectListItem>();
            list.Add(new SelectListItem { Selected = true, Text = "....เลือกหมวดพัสดุ....", Value = "" });

            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<MasResourceSubGroup> query = context.MasResourceSubGroups;

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    foreach (var model in x)
                    {
                        var item = new SelectListItem { Text = model.Name, Value = model.ResSubGrpId.ToString() };
                        list.Add(item);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }

        public IList<SelectListItem> SelectListMasResourceItem(MasResourceItem criteriaModel = null)
        {
            var list = new List<SelectListItem>();
            list.Add(new SelectListItem { Selected = true, Text = "....เลือกรายการพัสดุ....", Value = "" });

            try
            {
                using (var context = new GERPContext(false))
                {
                    IQueryable<MasResourceItem> query = context.MasResourceItems;

                    if (criteriaModel != null)
                    {
                        object[] param;
                        query = query.Where(KPIS.GERP.Models.Class.Utility.GenerateWhereString(criteriaModel, out param), param);
                    }

                    var x = query.ToList();
                    foreach (var model in x)
                    {
                        var item = new SelectListItem { Text = model.Name, Value = model.ResItemId.ToString() };
                        list.Add(item);
                    }
                }

            }
            catch (Exception ex)
            {
                throw ex;
            }

            return list;
        }

        #endregion


    }
}
